home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Power Programmierung
/
Power-Programmierung (Tewi)(1994).iso
/
magazine
/
dbms_mag
/
9103
/
techtip1.mar
< prev
next >
Wrap
Text File
|
1991-01-30
|
1KB
|
48 lines
Listing 1 (Darling, March)
REM PickSort. SQL
REM User-selectable sort orders in ORACLE's SQL*Plus
REM Charles B. Darling
REM Better Business Solutions, Inc. Clearwater, FL
REM Since SQL*Plus can't loop, the menu is necessarily pretty crude:
PROMPT 1. Name
PROMPT 2. Mail Stop
PROMPT 3. ZIP Code
PROMPT
ACCEPT Choice NUMBER PROMPT 'What order would like the list in?'
REM Print the list per the user's chioce:
SELECT FirstName, LastName, MailStop, ZIP
FROM Members
ORDER BY DECODE(&Choice, 1, LastName, 2, MailStop, 3, ZIP);
REM You can also do compound sorts:
PROMPT 1. Last Name, First Name
PROMPT 2. Mail Stop, Last Name
PROMPT 3. ZIP Code, Mail Stop, Last Name
PROMPT
ACCEPT Choice NUMBER PROMPT 'What order would you like the list in? '
REM Print the list per the user's choice:
SELECT FirstName, LastName, MailStop, ZIP
FROM Members
ORDER BY DECODE(&Choice, 1, LastName, 2, MailStop, 3, Zip),
DECODE(&Choice, 1, FirstName 2, LastName, 3, MailStop),
DECODE(&Choice, 1, '', 2, '', 3, LastName);
REM Though using the concatenation operator is more efficient:
SELECT FirstName, LastName, MailStop, ZIP
FROM Members
ORDER By DECODE(&Choice, 1, LastName::FirstName,
2, MailStop::LastName,
3, ZIP::MailStop::LastName);~~~~~